package com.jdbc.student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 实现记录的插入、删除、查询、修改
 * @author Enginner-Jie
 */
public class Function {
	
	/**
	 * 增、删、改功能 
	 * @param sql
	 */
	public static void update(String sql) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = JDBCUtil.getConnection();
			statement = connection.createStatement();
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(null,statement, connection);
		}
	}
	/**
	 * 插入记录功能  v2.0
	 * 使用PreparedStatement执行预编译的SQL语句
	 * @param sql
	 */
	public static void insert(int type, String iDCard, String examCard, String studentName, String location, int grade) {
		Connection connection = null;
		PreparedStatement pStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			
			String sql = "INSERT examstudent (Type,IDCard,ExamCard,StudentName,Location,Grade) VALUES (?,?,?,?,?,?);";
			pStatement = connection.prepareStatement(sql);
			
			pStatement.setInt(1, type);
			pStatement.setString(2, iDCard);
			pStatement.setString(3, examCard);
			pStatement.setString(4, studentName);
			pStatement.setString(5, location);
			pStatement.setInt(6, grade);
			
			pStatement.executeUpdate();
			Function.query("SELECT * from examstudent where IDcard = '"+iDCard+"';");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(null, pStatement, connection);
		}
	}
	/**
	 * 查询功能
	 * @param sql
	 */
	public static void query(String sql){
		Connection connection = null;
		Statement statement = null;
		ResultSet rSet = null;
		
		try {
			connection = JDBCUtil.getConnection();
			statement = connection.createStatement();
			rSet = statement.executeQuery(sql);
			
			if(rSet.next()){
				Student stu = new Student(); 
				
				stu.setFlowID(rSet.getInt(1));
				stu.setType(rSet.getInt(2));
				stu.setIDCard(rSet.getString("IDCard"));
				stu.setExamCard(rSet.getString("ExamCard"));
				stu.setStudentName(rSet.getString("StudentName"));
				stu.setLocation(rSet.getString("Location"));
				stu.setGrade(rSet.getInt(7));
				
				System.out.println(stu);
				
			}else {
				System.out.println("查无此人！");
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.release(rSet, statement, connection);
		}
	}
}
